package com.csyf.manager.dao;

import com.csyf.manager.entity.SysTrade;
import com.csyf.manager.entity.TradeChartVo;
import com.csyf.manager.entity.TradeFlowVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import tk.mybatis.mapper.common.Mapper;

import java.math.BigDecimal;
import java.util.List;

public interface SysTradeDao extends Mapper<SysTrade> {




    @Select("SELECT IFNULL(SUM(IFNULL(profit,0)),0) FROM `sys_trade` WHERE trade_status IN ('TRADE_SUCCESS','TRADE_FINISHED') AND mch_id = #{mch_id} AND service_type in ('XFZF_JD','XFZF_JD_H5') AND pay_time BETWEEN CONCAT(#{date},' 00:00:00') AND CONCAT(#{date},' 23:59:59')")
    BigDecimal sumJDtotalProfit(@Param("mch_id") String mch_id, @Param("date") String date);


    /**
     * 查询 业务类型总的收益
     *
     * @param mch_id 商户号
     * @param date  yyyy-MM-dd
     * @param services 'aa','bb','cc'
     * @return
     */
    @Select("SELECT IFNULL(SUM(IFNULL(profit,0)),0) FROM `sys_trade` WHERE trade_status IN ('TRADE_SUCCESS','TRADE_FINISHED') AND mch_id = #{mch_id} AND service_type in (${services}) AND pay_time BETWEEN CONCAT(#{date},' 00:00:00') AND CONCAT(#{date},' 23:59:59')")
    BigDecimal sumtotalProfitByServices(@Param("mch_id") String mch_id, @Param("date") String date, @Param("services") String services);



    /**
     * 查询 业务类型总的收益 时间段的
     *
     * @param mch_id 商户号
     * @param begintime  yyyy-MM-dd
     * @param endtime  yyyy-MM-dd
     * @param services 'aa','bb','cc'
     * @return
     */
    @Select("SELECT IFNULL(SUM(IFNULL(profit,0)),0) FROM `sys_trade` WHERE trade_status IN ('TRADE_SUCCESS','TRADE_FINISHED') AND mch_id = #{mch_id} AND service_type in (${services}) AND pay_time BETWEEN #{begintime} AND #{endtime}")
    BigDecimal sumtotalProfitByServicesAndBetweenTime(@Param("mch_id") String mch_id, @Param("services") String services, @Param("begintime") String begintime, @Param("endtime") String endtime);


    @Select("SELECT IFNULL(SUM(IFNULL(total_fee,0)),0) FROM `sys_trade` WHERE trade_status IN ('TRADE_SUCCESS','TRADE_FINISHED') AND mch_id = #{mch_id} AND service_type in (${services}) AND pay_time BETWEEN CONCAT(#{date},' 00:00:00') AND CONCAT(#{date},' 23:59:59')")
    BigDecimal sumtotalTradeByServices(@Param("mch_id") String mch_id, @Param("date") String date, @Param("services") String services);


    @Select("SELECT IFNULL(SUM(IFNULL(total_fee,0)),0) FROM `sys_trade` WHERE trade_status = 'TRADE_SUCCESS' AND src_mch_id = #{srcMchid} AND create_time BETWEEN CONCAT(#{date},' 00:00:00') AND CONCAT(#{date},' 23:59:59')")
    BigDecimal sumtotalTradeBySrcMchId(@Param("date") String date, @Param("srcMchid") String srcMchid);


    /**
     * 根据查询条件统计订单金额和商户收益
     * @param vo
     * @return
     */
    @SelectProvider(type = OrderSqlProvider.class, method = "sumTradeFee")
    TradeDto queryTotalFeeByCondition(SysTrade vo);

    /**
     * 查询当日总交易、
     * 当日商户总收益
     * 当日手续费、
     * 全部订单总交易、
     * 全部商户总收益、
     * 全部手续费
     * @return
     */
    @Select("SELECT \n" +
            "IFNULL(SUM(IFNULL(total_fee,0)),0) AS todayTotalFee,\n" +
            "IFNULL(SUM(IFNULL(profit,0)),0) AS todayProfit,\n" +
            "IFNULL(SUM(IFNULL(total_fee,0)),0)-IFNULL(SUM(IFNULL(profit,0)),0) AS todayHandlingFee\n" +
            "FROM sys_trade \n" +
            "WHERE trade_status = 'TRADE_SUCCESS' AND DATEDIFF(create_time,NOW())=0")
    TradeFlowVo sumTradeFlowBydate();

    /**
     * 首页图表统计
     */
    @Select("SELECT\n" +
            " sum(total_fee) AS totalFee,\n" +
            " DATE_FORMAT(create_time, '%Y-%m-%d') AS tradeTime\n" +
            " FROM sys_trade\n" +
            " WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)\n" +
            " AND trade_status = 'TRADE_SUCCESS'\n" +
            " GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')")
    List<TradeChartVo> buildChartBydate();

}
